1. Technical Field
This invention generally relates to computer database systems, and more specifically relates to apparatus and methods for autonomic refresh of a materialized query table in a computer database.
2. Background Art
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. Data is typically stored in database tables. The tables contain columns and rows of data. The data in the table is related to or associated with other data in corresponding columns and rows. Relationships of the data are stored in indexes.
Retrieval of information from a database is typically done using queries. A database query typically includes one or more predicate expressions interconnected with logical operators. The database is searched for records that satisfy the query, and those records are returned as the query result. In database systems it is common for identical or closely related queries to be issued frequently. When a database contains very large amounts of data, certain queries against the database can take an unacceptably long time to execute. The cost of executing a query may be particularly significant when the query requires join operations among a large number of database tables.
It has become a common practice to store the results of often-repeated queries in database tables. By storing the results of queries, the costly join operations required to generate the results do not have to be performed every time the queries are issued. Rather, the database server responds to the queries by simply retrieving the pre-stored data. These stored results are sometimes referred to as a materialized views or materialized query tables (MQT). The purpose for the MQT is to provide an aggregation of data that can satisfy many subsequent queries without repeating the full access to the database.
As new data is periodically added to the base tables of a materialized query table, the materialized query table needs to be updated to reflect the new base table data. When a materialized query table accurately reflects all of the data currently in its base tables, the materialized query table is considered to be “fresh”. Otherwise, the materialized query table is considered to be “stale”. A stale materialized query table may be re-computed by various techniques that are collectively referred to as a “refresh”. Some prior art systems use different modes to tolerate data staleness. For example, software may access the MQT in Enforced mode, Trusted mode, or Stale-Tolerated mode. When software accesses the data in Enforced mode, the data is required to be 100% accurate. If the MQT is not up to date when accessed in this mode, the data must be retrieved from the base tables rather than from the MQT. Retrieving the data from the base tables is more costly in system resources and in some cases may actually be more costly than updating the MQTs.
Without a way to update an MQT in an efficient manner, the computer industry will continue to suffer from inefficiency and poor database performance.